const express = require("express")
const app = express()

var mysql      = require('mysql');
var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'studentmanage',
    timezone:"SYSTEM"
});

connection.connect();

//跨域请求
app.all('*', function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "X-Requested-With");
    res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS");
    res.header("X-Powered-By",' 3.2.1')
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
});



//删除课程
app.get("/course/delete",(req, res) => {
    console.log("=======ok");
    let params = req.query;
    let value = [params.cid];
    console.log(params);
    let sql = `DELETE FROM tbl_course where cid=?`;
    //删除关系数据表
    if(params.cid){
        let sql2 = `delete from tbl_student_course where cid = ${value}`;
        connection.query(sql2,value,function (error, results, fields) {
            if (error) throw error;
        });
    }
    connection.query(sql,value,function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ',results);
        res.status(200).send(results);
    });


});
//课程信息修改
app.get("/course/edit",(req, res) =>{
    let params = req.query.arr;
    console.log(params);
    console.log("成功进入课程修改");
    let sql = `UPDATE tbl_course SET cname=?,cnum=?,ctime=?,cremark=? where cid=?`;
    connection.query(sql,params,function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ',results);
        res.redirect(200,"http://localhost:8080/#/Course")
    });
});
//学习课程修改单值查询
app.get("/course/toedit",(req,res)=>{
    let sql = "SELECT * from tbl_course where cid=?";
    let params=req.query.cid;
    console.log("成功进入修改查询操作ok")
    console.log(sql);
    connection.query(sql,params,function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ',results);
        res.status(200).send(results);

    });
})
//多选删除
app.get("/course/removeCk",(req, res) => {
    console.log("=======ok");
    let params = req.query;
    let delArray = params.delArray;
    for(var i=0;i<delArray.length;i++){
        let where_value = delArray[i];
        //删除关系数据表
        let sql2 = `delete from tbl_student_course where cid = ${where_value}`;
        connection.query(sql2,where_value,function (error, results, fields) {
            if (error) throw error;
        });

        let sql = `DELETE FROM tbl_course where cid=?`;
        connection.query(sql,where_value,function (error, results, fields) {
            if (error) throw error;
            console.log('The solution is: ',results);
        });
    }
    // res.redirect(200,"http://localhost/index.html");
});
//新增学生
app.get("/course/save",(req, res) => {
    console.log("成功进入课程新增");
    let params = req.query.arr;
    console.log(params);
    let sql = `insert into tbl_course values (?,?,?,?,?)`;
    connection.query(sql,params,function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ',results);
    });

});

//学生信息表学号查询
app.get("/course/counoid",(req, res) => {
    console.log("=======ok");
    let sql = `SELECT MAX(cid) cid FROM tbl_course`;
    connection.query(sql,function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ',results);
        res.send(results);
    });
});
//统计总数据
app.get("/countpage",(req, res) => {
    let sql = `select count(*) pgCount from tbl_course`;
    connection.query(sql,function (error, results, fields) {
        if (error) throw error;
        res.status(200).send(results);
    });
})

//查询
app.get("/course",(req,res)=>{
    let sql = `SELECT * from tbl_course where 1=1`;
    let params=req.query;
    let value=[];
    console.log();
    console.log("------------ok");
     if(params.CidAndName){
         value=[params.CidAndName]
         sql+=` and cid=? or cname like '%${value}%'`;
     }
    //存储前台拿到的页面数据
    //每页页数
    value.push(params.Pages);
    console.log(value+"---------------")
    sql+=` limit ${params.Pages},${params.Count}`;
    console.log(sql);
    connection.query(sql,value,function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ',results);
        res.status(200).send(results);

    });
})

app.listen(8002,()=>{
    console.log("Server running at http://localohost:8002/")
})